package com.secsbrain.cmdb.biz.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import com.secsbrain.common.utils.DateTimeUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 读取Excel工具
 * @author zhaoyi
 * 
 */
public class ExcelReadUtil {
    
	/***
	 * 获取工作Workbook
	 * 
	 * @return
	 */
	public static Workbook getWorkbook(InputStream inputStream,boolean flag) {
	    Workbook workBook = null;
		try {
		    if(flag){
		        workBook = new HSSFWorkbook(inputStream);
		    }else{
		        workBook = new XSSFWorkbook(inputStream);
		    }
		} catch (IOException e) {
			e.printStackTrace();
		}
		return workBook;
	}
	
	/***
     * 获取工作Workbook
     * 
     * @return
     */
    @SuppressWarnings("deprecation")
    public static Workbook getWorkbook(String filePath,boolean flag) {
        Workbook workBook = null;
        try {
            if(flag){
                File f = new File(filePath);  
                FileInputStream is = new FileInputStream(f);  
                POIFSFileSystem fs = new POIFSFileSystem(is);     
                workBook = new HSSFWorkbook(fs);  
                is.close();  
            }else{
                workBook = new XSSFWorkbook(filePath);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workBook;
    }

	/**
	 * 根据excel表的索引获取工作表
	 * 
	 * @param workBook excel文件
	 * @param sheelIndex 工作表的索引
	 * @return
	 */
	public static Sheet getSheetByIndex(Workbook workBook,int sheelIndex) {
	    Sheet sheet = null;
		if (workBook != null) {
			try {
				sheet = workBook.getSheetAt(sheelIndex);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return sheet;
	}

	/**
	 * 根据excel表的名称获取工作表
	 * 
	 * @param workBook
	 * @param sheelName
	 * @return
	 */
	public static Sheet getSheetByName(Workbook workBook,String sheelName) {
	    Sheet sheet = null;
		if (workBook != null) {
			try {
				sheet = workBook.getSheet(sheelName);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return sheet;
	}

	/***
	 * 获取excel所有工作表
	 * 
	 * @param workBook
	 * @return
	 */
	public static List<Sheet> getAllSheet(Workbook workBook) {
		List<Sheet> list = null;
		if (workBook != null) {
			try {
				if (workBook != null) {
					if (workBook.getNumberOfSheets() > 0) {
						list = new ArrayList<Sheet>();
						for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
							list.add(workBook.getSheetAt(i));
						}
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/***
	 * 获取某一行
	 * 
	 * @param sheet
	 * @param rowIndex
	 * @return
	 */
	public static Row getRowByIndex(Sheet sheet, int rowIndex) {
	    Row row = null;
		try {
			if (sheet != null) {
				if (rowIndex > 0 && rowIndex <= (sheet.getLastRowNum() + 1)) {
					row = sheet.getRow(rowIndex - 1);
				} else {
					return null;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return row;
	}

	/**
	 * 获取所有行
	 * 
	 * @param sheet
	 * @return
	 */
	public static List<Row> getAllRow(Sheet sheet) {
		List<Row> rows = null;
		try {
			if (sheet != null) {
				if (sheet.getLastRowNum() > 0) {
					rows = new ArrayList<Row>();
					for (int i = 0; i <= sheet.getLastRowNum(); i++) {
						if (sheet.getRow(i) != null) {
							rows.add(sheet.getRow(i));
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rows;
	}

	/**
	 * 获取工作表的哪几行
	 * 
	 * @param sheet
	 * @param sstart 开始位置
	 * @param send 结束位置
	 * @return 集合
	 */
	public static List<Row> getRowByStartAndEnd(Sheet sheet,String sstart, String send) {
		int start = Integer.parseInt(sstart == null ? "0" : sstart);
		int end = Integer.parseInt(send == null ? "0" : send);
		List<Row> rows = null;
		try {
			if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) {
				rows = new ArrayList<Row>();
				if (start == 0 && end == 0) {
					for (int i = 0; i <= sheet.getPhysicalNumberOfRows(); i++) {
						if (sheet.getRow(i) != null) {
							rows.add(sheet.getRow(i));
						}
					}
				} else if (start <= sheet.getPhysicalNumberOfRows() + 1 && end == 0
						&& start > 0) {
					for (int i = start - 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
						if (sheet.getRow(i) != null) {
							rows.add(sheet.getRow(i));
						}
					}
				} else if (start <= sheet.getPhysicalNumberOfRows() + 1 && end > 0
						&& end <= sheet.getPhysicalNumberOfRows() + 1 && end >= start
						&& start > 0) {
					for (int i = start - 1; i <= end - 1; i++) {
						if (sheet.getRow(i) != null) {
							rows.add(sheet.getRow(i));
						}
					}
				} else if (start > 0 && end <= sheet.getPhysicalNumberOfRows() + 1
						&& start <= sheet.getPhysicalNumberOfRows() + 1
						&& start == end) {
					for (int i = start - 1; i <= end - 1; i++) {
						if (sheet.getRow(i) != null) {
							rows.add(sheet.getRow(i));
						}
					}
				} else if (start > end) {
					for (int i = 0; i <= sheet.getPhysicalNumberOfRows(); i++) {
						if (sheet.getRow(i) != null) {
							rows.add(sheet.getRow(i));
						}
					}
				} else {
					for (int i = start; i <= sheet.getPhysicalNumberOfRows(); i++) {
						if (sheet.getRow(i) != null) {
							rows.add(sheet.getRow(i));
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return rows;
	}

	/***
	 * 获取某一行的所有单元格
	 * 
	 * @param HSSFsheet
	 * @param rowIndex
	 * @return
	 */
	public static List<Cell> getOneRowCells(Sheet sheet,int rowIndex) {
		List<Cell> cells = null;
		try {
		    Row row = ExcelReadUtil.getRowByIndex(sheet, rowIndex);
			if (row != null) {
				if (row.getLastCellNum() > 0) {
					cells = new ArrayList<Cell>();
					for (int i = 0; i < row.getLastCellNum(); i++) {
						cells.add(row.getCell((short) i));
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cells;
	}

	/***
	 * 获取某一行的所有单元格的内容
	 * 
	 * @param HSSFsheet
	 * @param rowIndex
	 * @return
	 */
	public static String[] getOneRowCellsContext(Sheet sheet,int rowIndex) {
		String[] cells = null;

		try {
		    Row row = ExcelReadUtil.getRowByIndex(sheet, rowIndex);
			if (row != null) {
				if (row.getLastCellNum() > 0) {
					cells = new String[row.getLastCellNum()];
					for (int i = 0; i < row.getLastCellNum(); i++) {
						cells[i] = ExcelReadUtil.getCellContent(row
								.getCell((short) i));
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cells;

	}
	/***
	 * 获取某一行的所有单元格
	 * @param inputstream
	 * @param rowIndex
	 * @param sheelIndex
	 * @return
	 */
	public static String[] getOneRowCellsContextIs(InputStream inputstream,boolean flag,int rowIndex, int sheelIndex) {
	    Workbook workBook = ExcelReadUtil.getWorkbook(inputstream,flag);
		return ExcelReadUtil.getOneRowCellsContext(ExcelReadUtil.getSheetByIndex(workBook, sheelIndex), rowIndex);
	}
	
	/**
	 * 
	 * 获取某一行第几个单元格
	 * 
	 * @param sheet
	 * @param rowIndex 行索引
	 * @param cellIndex 列索引
	 * @return 单元格对象
	 */
	public static Cell getOneCell(Sheet sheet, int rowIndex,
			int cellIndex) {
	    Cell cell = null;
		try {
		    Row row = ExcelReadUtil.getRowByIndex(sheet, rowIndex);
			if (row != null && cellIndex > 0 && cellIndex <= row.getLastCellNum()) {
				cell = row.getCell((short) (cellIndex - 1));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cell;
	}

	/*****
	 * 获取哪几行的哪几个单元格
	 * 
	 * @param sheet
	 * @param scellstart
	 * @param scellend
	 * @param srowstart
	 * @param srowend
	 * @return
	 */
	public static List<Cell> getCellsByStartAndEnd(Sheet sheet,
			String scellstart, String scellend, String srowstart, String srowend) {
		int cellstart = Integer.parseInt(scellstart == null ? "0" : scellstart);
		int cellend = Integer.parseInt(scellend == null ? "0" : scellend);
		List<Cell> cells = null;
		List<Row> rows = ExcelReadUtil.getRowByStartAndEnd(sheet,srowstart, srowend);
		try {
			if (rows != null && rows.size() > 0) {
				cells = new ArrayList<Cell>();
				for (int i = 0; i < rows.size(); i++) {
				    Row row = rows.get(i);
					if (cellstart == 0 && cellend == 0) {
						for (int j = 0; j < row.getLastCellNum(); j++) {
							cells.add(row.getCell((short) j));
						}
					} else if (cellstart > 0 && cellend <= row.getLastCellNum()
							&& cellend > 0 && cellstart <= cellend) {
						for (int j = cellstart - 1; j <= (cellend - 1); j++) {
							cells.add(row.getCell((short) j));
						}

					} else if (cellstart > 0
							&& cellstart <= row.getLastCellNum()
							&& cellend == 0) {
						for (int j = cellstart - 1; j < row.getLastCellNum(); j++) {
							cells.add(row.getCell((short) j));
						}
					} else {
						for (int j = 0; j < row.getLastCellNum(); j++) {
							cells.add(row.getCell((short) j));
						}
					}
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return cells;
	}

	/**
	 * 获取单元格内容
	 * 
	 * @param cell
	 * @return
	 */
	public static String getCellContent(Cell cell) {
		String str = "";
		
		int type = cell.getCellType();
		switch (type) {
		case Cell.CELL_TYPE_NUMERIC:
		    if(DateUtil.isCellDateFormatted(cell)){
		        str = DateTimeUtils.parseDateAsString(cell.getDateCellValue(), "yyyyMMddHHmmss");
            }else{ 
    			DecimalFormat df = new DecimalFormat("0.00"); 
    			str = killzero(df.format(cell.getNumericCellValue()));
            }
			break;
		case Cell.CELL_TYPE_FORMULA:
			str = String.valueOf(cell.getCellFormula());
			break;
		case Cell.CELL_TYPE_STRING:
			str = String.valueOf(cell.getStringCellValue());
			break;
		case Cell.CELL_TYPE_BLANK:
			str="";
		default:
			break;
		}
		return str;
	}

	/***
	 * 获取指定单元格内容
	 * 
	 * @param HSSFsheet
	 * @param scellstart
	 * @param scellend
	 * @param srowstart
	 * @param srowend
	 * @return
	 */
	public static List<String[]> getCellsByStartAndEndContext(
			Sheet sheet, String scellstart, String scellend,
			String srowstart, String srowend) {
		int cellstart = Integer.parseInt(scellstart == null ? "0" : scellstart);
		int cellend = Integer.parseInt(scellend == null ? "0" : scellend);
		List<String[]> cells = new ArrayList<String[]>();
		List<Row> rows = ExcelReadUtil.getRowByStartAndEnd(sheet, srowstart, srowend);
		try {
			if (rows != null && rows.size() > 0) {
				String[] str = null;
				for (int i = 0; i < rows.size(); i++) {

				    Row row = rows.get(i);
					if (cellstart == 0 && cellend == 0) {
						int s = 0;
						str = new String[row.getLastCellNum()];
						for (int j = 0; j < row.getLastCellNum(); j++) {
							if(row.getCell(j)!=null){
								str[s] = ExcelReadUtil.getCellContent(row.getCell(j));
							}else{
								str[s]="";
							}
							
							s++;
						}
					} else if (cellstart > 0 && cellend <= row.getLastCellNum()
							&& cellend > 0 && cellstart <= cellend) {
						int s = 0;
						str = new String[((cellend - cellstart) < 0 ? 0 : (cellend - cellstart)) + 1];
						for (int j = cellstart - 1; j <= (cellend - 1); j++) {
							if(row.getCell(j)!=null){
								str[s] = ExcelReadUtil.getCellContent(row.getCell(j));
							}else{
								str[s]="";
							}
							s++;
						}
					} else if (cellstart > 0
							&& cellstart <= row.getLastCellNum()
							&& cellend == 0) {
						int s = 0;
						str = new String[row.getLastCellNum() - cellstart + 1];
						for (int j = cellstart - 1; j < row.getLastCellNum(); j++) {
							if(row.getCell(j)!=null){
								str[s] = ExcelReadUtil.getCellContent(row.getCell(j));
							}else{
								str[s]="";
							}
							s++;
						}
					} else {
						int s = 0;
						str = new String[row.getLastCellNum()];
						for (int j = 0; j < row.getLastCellNum(); j++) {
							if(row.getCell(j)!=null){
								str[s] = ExcelReadUtil.getCellContent(row.getCell(j));
							}else{
								str[s]="";
							}
							s++;
						}
					}
					cells.add(str);
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return cells;
	}

	/***
	 * 解析excel
	 * 
	 * @param inputstream  excel数据流
	 * @param scellstart   开始单元格为NULL时默认从0格开始
	 * @param scellend     每一行的结束单元格为Null表示最后格
	 * @param srowstart    开始行
	 * @param srowend      结束行
	 * @param sheelIndex
	 * @return
	 */
	public static List<String[]> getAllExcelInfo(InputStream inputstream,boolean flag,
			String scellstart, String scellend, String srowstart,
			String srowend, int sheelIndex) {
	    Workbook workBook = ExcelReadUtil.getWorkbook(inputstream,flag);
		return ExcelReadUtil.getCellsByStartAndEndContext(
				ExcelReadUtil.getSheetByIndex(workBook, sheelIndex),
				scellstart, scellend, srowstart, srowend);
	}
	
	
	/***
	 * 解析excel
	 * @param filepath
	 * @param scellstart
	 * @param scellend
	 * @param srowstart
	 * @param srowend
	 * @param sheelIndex
	 * @return
	 */
	public static List<String[]> getAllExcelInfo(String filepath,boolean flag,
			String scellstart, String scellend, String srowstart,
			String srowend, int sheelIndex) {
	    
	    Workbook workBook = ExcelReadUtil.getWorkbook(filepath,flag);
		return ExcelReadUtil.getCellsByStartAndEndContext(
				ExcelReadUtil.getSheetByIndex(workBook, sheelIndex),
				scellstart, scellend, srowstart, srowend);
	}
	
	/**
	 * 去掉没有意义小数位0
	 * 
	 * @param str
	 * @return
	 */
	public static String killzero(final String str) {
		if (StringUtils.isBlank(str)) {
			return "";
		}

		String resultStr = str;
		while (resultStr.indexOf(".") != -1
				&& resultStr.charAt(resultStr.length() - 1) == '0') {
			resultStr = resultStr.substring(0, resultStr.length() - 1);
		}

		if (resultStr.charAt(resultStr.length() - 1) == '.') {
			resultStr = resultStr.substring(0, resultStr.length() - 1);
		}
		return resultStr;
	}

	public static void main(String[] args) throws Exception {
		InputStream fis = new FileInputStream(new File("E:\\集群信息整理.xlsx"));
		List<String[]> cells = ExcelReadUtil.getAllExcelInfo(fis,false, null, null, "2", null, 0);
		for (int j = 0; j < cells.size(); j++) {
			for (int i = 0; i < cells.get(j).length; i++) {
				System.out.println(cells.get(j)[i]);

			}

		}
	}
}